package com.entrogy.dao;

import com.entrogy.bean.Appraisal;
import com.entrogy.bean.Attendance;
import com.entrogy.bean.PassRecordEntity;

import java.util.List;
import java.util.Map;

/**  考勤dao接口
 * @Author 许红松
 * @date 2019/5/9 0009 上午 10:42
 */
public interface AppraisalMapper {

    /**
     *  查询是否存在某张表
     * @param table_name
     * @return
     */
    public int selectIstable(String table_name);

    /**
     *  根据school_code 查询dingding E应用配置
     * @param school_code
     * @return
     */
    public int selectDingDingConfig(int school_code);

    /**
     *   查询数据
     * @param map
     * @return
     */
    public List<String>  getAppraisalList(Map<String,Object> map);

    /**
     *  批量新增
     * @param map
     * @return
     */
    public  int  addBachAppraisal(Map<String,Object> map);


    /**
     *  查询打卡流水信息
     * @param map
     * @return
     */
    public List<Appraisal>  selectAppraisalByList(Map<String,Object> map);
    /**
     *  查询总数
     *
     * @return
     */
    public  Integer total(Map<String,Object> map);

    /**
     *  创建本月的考勤表
     * @param table_name
     * @return
     */
    public int createtable(String table_name);

    /**
     *  钉钉查询打卡流水信息
     * @param map
     * @return
     */
    public List<Appraisal>  getAppraisalByList(Map<String,Object> map);


    /**
     *   根据考勤Id  查询详情
     * @param map
     * @return
     */
    public  Appraisal  selectAppraisalById(Map<String,Object> map);

    /**
     *  批量新增
     * @param map
     * @return
     */
    public  int  addBachAttendance(Map<String,Object> map);


    /**
     *  钉钉查询打卡结果信息
     * @param map
     * @return
     */
    public List<String>  getAttendanceList(Map<String,Object> map);
    /**
     *  钉钉查询打卡结果信息列表
     * @param map
     * @return
     */
    public List<Attendance>  getAttendanceByList(Map<String,Object> map);

    /**
     *  查询总数
     *
     * @return
     */
    public  Integer getTotal(Map<String,Object> map);



    /**
     *  钉钉查询打卡结果信息
     * @param map
     * @return
     */
    public List<Attendance>  getAttendanceList1(Map<String,Object> map);


    /**
     *  获取当天的考勤数据
     * @return
     */
    public  List<PassRecordEntity>   getPassRecordList1(Map<String,Object> map);


    /**
     *  根据人员查询他本人当天的考勤时间
     * @param map
     * @return
     */
    public  Map<String,Object>   getshiftBypeople(Map<String,Object> map);


    /**
     *  修改状态
     * @param map
     * @return
     */
    public int  updateStype1(Map<String,Object> map);


    /**
     *  查询统计所有
     * @param map
     * @return
     */
    public List<Map<String,Object>>   getStatisticsList(Map<String,Object> map);

    /**
     *  查询出勤天数所有
     * @param map
     * @return
     */
    public List<String> getCount1List(Map<String,Object> map);


    /**
     *  钉钉查询打卡结果信息
     * @param map
     * @return
     */
    public List<String>  getAttendanceList2(Map<String,Object> map);

    /**
     *  创建本月的考勤表
     * @param table_name
     * @return
     */
    public int createtable1(String table_name);
    // 按日查询记录
   /* public List<PassRecordBean> selectDay(String tableName) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        String sql = "select  aa.teacher_name,aa.machine_name,aa.record_time,d.duty_name,aa.person_id  from duty_information d,(select  p.person_id,t.admin_duty_id,t.teacher_name,p.machine_name,p.record_time,p.flag from teacher_information t inner join "
                + tableName
                + " p on t.teacher_thdid = p.person_id where t.isAttendance = 1 and p.machine_name like '%DT%' GROUP BY p.person_id) aa where aa.admin_duty_id = d.duty_id";
        try {
            List<PassRecordBean> passRecordList = queryRunner.query(sql,
                    new BeanListHandler<PassRecordBean>(PassRecordBean.class));
            return passRecordList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PassRecordBean getAfternoonTime(String tableName, String person_id, String morningOutTime,
                                           String afternoonTime) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        String sql = "select count(*)  from " + tableName
                + " p where  p.machine_name like '%DT%' and p.person_id = ? and ? <= p.record_time and ?>= p.record_time";
        String sql2 = "select *  from " + tableName
                + " p where  p.machine_name like '%DT%' and p.person_id = ? and ? <= p.record_time and ?>= p.record_time order by p.record_time desc LIMIT 1";
        try {
            Long count = (Long) queryRunner.query(sql, new ScalarHandler(1), person_id, morningOutTime, afternoonTime);
            if (count.intValue() < 2) { // 中午的打卡 没有 两次以上 所以记录 他 下午上班 没有打卡
                return null;
            }
            return queryRunner.query(sql2, new BeanHandler<>(PassRecordBean.class), person_id, morningOutTime,
                    afternoonTime);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PassRecordBean getMorningOutTime(String tableName, String person_id, String morningOutTime) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        String sql = "select * from " + tableName
                + " p where  p.machine_name like '%DT%' and p.person_id = ? and ? <= p.record_time limit 1";
        try {
            return queryRunner.query(sql, new BeanHandler<>(PassRecordBean.class), person_id, morningOutTime);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 下午下班时间后的第一次的刷脸 时间
    public PassRecordBean getAfternoonOutTime(String tableName, String person_id, String afternoonOutWork) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        String sql = "select * from " + tableName
                + " p where  p.machine_name like '%DT%' and p.person_id = ? and p.record_time >= ?  limit 1";
        try {
            return queryRunner.query(sql, new BeanHandler<>(PassRecordBean.class), person_id, afternoonOutWork);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 根据月份去判断有没有这个月的统计数据
    public int checkMonthTable(String tableName) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        String sql = "select * from pass_record_" + tableName + " limit 1";
        try {
            PassRecordBean passRecordBean = queryRunner.query(sql,
                    new BeanHandler<PassRecordBean>(PassRecordBean.class));
            return 1;
        } catch (SQLException e) {
            return 0;
        }
    }

    // 根据月份创建数据表
    public void createMonthTable(String month) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        String sql = "CREATE TABLE `pass_record_" + month + "` (" + "  `id` int(32) NOT NULL AUTO_INCREMENT ,"
                + "  `record_id` int(11) DEFAULT NULL," + "  `person_id` varchar(32) DEFAULT NULL,"
                + "  `person_name` varchar(45) DEFAULT NULL," + "  `machine_name` varchar(32) DEFAULT NULL,"
                + " `isNormal` int(2) DEFAULT 0," + "  `record_time` datetime DEFAULT NULL," + "  PRIMARY KEY (`id`)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat sFormat = new SimpleDateFormat("HH:mm");
            queryRunner.update(sql);
            List<String> days = getMonthDays(month);
            // 按每月的天数 去每天生成的表里查询数据
            for (String day : days) {
                String tableName = "pass_record" + day;
                // 所有的考勤 人员 信息
                String sql2 = "select * from (select * from " + tableName
                        + " p,teacher_information t where t.isAttendance = 1 and p.person_id = t.teacher_thdid and machine_name like '%DT%') pp ,duty_information d where pp.admin_duty_id = d.duty_id";
                // 查询出每天的考勤打卡数据
                List<PassRecordBean> passRecordBeans = queryRunner.query(sql2,
                        new BeanListHandler<>(PassRecordBean.class));
                String year = day.substring(0, 4) + "-";
                String monthTime = day.substring(4, 6) + "-";
                String dayTime = day.substring(6, 8);
                day = year + monthTime + dayTime;
                // 插入每天的打卡数据到每月的统计数据表中
                for (PassRecordBean passRecordBean : passRecordBeans) {
                    Date morningTime = null;// 上午上班打卡时间
                    Date morningOutTime = null;// 上午下班打卡时间
                    Date afternoonTime = null;// 下午上班打卡时间
                    Date afternoonOutTime = null;// 下午下班打卡时间
                    Date nightWork = null;// 晚上上班打卡时间
                    Date nightOutWork = null;// 晚上下班打卡时间
                    // 查询出某个人 的打卡时间
                    ShiftBean shiftBean = new ShiftDao().getShiftByThdid(passRecordBean.getPerson_id(), day);
                    if (null != shiftBean) {
                        if (!StrUtils.isNullOrEmpty(shiftBean.getShift_time1())) {
                            morningTime = simpleDateFormat.parse(day + " " + shiftBean.getShift_time1());
                        }
                        if (!StrUtils.isNullOrEmpty(shiftBean.getShift_time2())) {
                            morningOutTime = simpleDateFormat.parse(day + " " + shiftBean.getShift_time2());
                        }
                        if (!StrUtils.isNullOrEmpty(shiftBean.getShift_time3())) {
                            afternoonTime = simpleDateFormat.parse(day + " " + shiftBean.getShift_time3());
                        }
                        if (!StrUtils.isNullOrEmpty(shiftBean.getShift_time4())) {
                            afternoonOutTime = simpleDateFormat.parse(day + " " + shiftBean.getShift_time4());
                        }
                        if (!StrUtils.isNullOrEmpty(shiftBean.getShift_time5())) {
                            nightWork = simpleDateFormat.parse(day + " " + shiftBean.getShift_time5());
                        }
                        if (!StrUtils.isNullOrEmpty(shiftBean.getShift_time6())) {
                            nightOutWork = simpleDateFormat.parse(day + " " + shiftBean.getShift_time6());
                        }

                        // 判断是否打卡正常
                        if (morningTime != null
                                && !Kaoqin2Servlet.sameTime(passRecordBean.getRecord_time(), morningTime, sFormat)
                                && morningTime.before(passRecordBean.getRecord_time())) {// 有早班在上班时间之后打的卡
                            passRecordBean.setIsNormal(1); // 0--正常 1--不正常
                        } else if (afternoonTime != null && morningTime == null
                                && !Kaoqin2Servlet.sameTime(passRecordBean.getRecord_time(), afternoonTime, sFormat)
                                && afternoonTime.before(passRecordBean.getRecord_time())) {// 没有早班 在上班时间之后打的卡
                            passRecordBean.setIsNormal(1); // 0--正常 1--不正常
                        } else if (morningTime != null && afternoonTime != null && passRecordBean.getIsNormal() != 1) {// 有早班和午班查看中午是否有两次打卡
                            checkClockCount(queryRunner, tableName, passRecordBean, morningOutTime, afternoonTime);
                        } else if (passRecordBean.getIsNormal() != 1) { // 去判断下班 打卡是否正常
                            PassRecordBean passRecordBean1 = null;
                            if (StrUtils.isNullOrEmpty(shiftBean.getShift_time5()) && afternoonTime != null) {// 没有加班时间
                                passRecordBean1 = getNightOutWork(tableName, passRecordBean.getPerson_id(),
                                        day + " " + shiftBean.getShift_time3());// 下午下班时间后 的第一次打卡时间
                            } else if (afternoonOutTime != null) {
                                passRecordBean1 = getAfternoonOutTime(tableName, passRecordBean.getPerson_id(),
                                        day + " " + shiftBean.getShift_time4());// 下班时间后 的最后一次打卡时间
                            }
                            if (passRecordBean1 == null) {
                                passRecordBean.setIsNormal(1); // 不正常
                            }
                        }
                        // 判断这个人是否有加班时间
                        if (!StrUtils.isNullOrEmpty(shiftBean.getShift_time5())) {// 有加班
                            if (passRecordBean.getIsNormal() != 1) {
                                checkClockCount(queryRunner, tableName, passRecordBean, afternoonOutTime, nightWork);
                            } else if (passRecordBean.getIsNormal() != 1) {
                                PassRecordBean passRecordBean2 = getNightOutWork(tableName,
                                        passRecordBean.getPerson_id(), day + " " + shiftBean.getShift_time6());// 下班时间后
                                // 的最后一次打卡时间
                                if (passRecordBean2 == null) {
                                    passRecordBean.setIsNormal(1); // 不正常
                                }
                            }
                        }
                        String sql3 = "insert into pass_record_" + month
                                + "(person_id,person_name,machine_name,record_time,isNormal) values(?,?,?,?,?)";
                        Object[] params = { passRecordBean.getPerson_id(), passRecordBean.getPerson_name(),
                                passRecordBean.getMachine_name(), passRecordBean.getRecord_time(),
                                passRecordBean.getIsNormal() };
                        queryRunner.update(sql3, params);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void checkClockCount(QueryRunner queryRunner, String tableName, PassRecordBean passRecordBean,
                                 Date morningOutTime, Date afternoonTime) throws SQLException {
        String sql3 = "select count(*) from " + tableName
                + " where person_id = ? and machine_name like '%DT%' and record_time >= ? and record_time <= ? ";
        Long counts = (Long) queryRunner.query(sql3, new ScalarHandler(1), passRecordBean.getPerson_id(),
                morningOutTime, afternoonTime);
        if (counts.intValue() < 2) {
            passRecordBean.setIsNormal(1); // 不正常
        }
    }

    *//**
     * <p>
     * Description: 获取月份的天数
     * </p>
     *
     * @param date 月份
     * @return List<String>
     *//*
    public List<String> getMonthDays(String date) {
        List<String> resultList = new ArrayList<String>();
        if (StrUtils.isNullOrEmpty(date)) {
            return resultList;
        }
        int year = Integer.valueOf(date.substring(0, 4));
        int month = Integer.valueOf(date.substring(4, 6));
        // 在使用set方法之前，必须先clear一下，否则很多信息会继承自系统当前时间
        Calendar time = Calendar.getInstance();
        time.clear();
        time.set(Calendar.YEAR, year);
        time.set(Calendar.MONTH, month - 1);// Calendar对象默认一月为0
        int days = time.getActualMaximum(Calendar.DAY_OF_MONTH);// 本月份的天数
        for (int i = 1; i <= days; i++) {
            String day = "";
            if (i < 10) {
                day = date.substring(0, 6) + "0" + i;
            } else {
                day = date.substring(0, 6) + i;
            }
            resultList.add(day);
        }
        return resultList;
    }

    // 根据月份查询打卡考勤的数据
    public List<TeacherBean> getPassRecordByMoth(String tableName) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        try {
            // 1.先查询出 考勤打卡的所有人员信息
            String sql = "select teacher_thdid,d.duty_name as teacher_nickname,teacher_name from teacher_information t,duty_information d where t.isAttendance = 1 and t.admin_duty_id = d.duty_id";
            List<TeacherBean> lists = queryRunner.query(sql, new BeanListHandler<TeacherBean>(TeacherBean.class));
            // 2.根据人员ID 按月去统计一个人的异常信息
            for (TeacherBean teacherBean : lists) {
                if (teacherBean.getTeacher_thdid() != null) {
                    String sql2 = "select count(*) from (select * from pass_record_" + tableName
                            + " where  person_id = ? and isNormal = 1 group by DATE_FORMAT(record_time,'%d')) aa";
                    Long counts = (Long) queryRunner.query(sql2, new ScalarHandler(1), teacherBean.getTeacher_thdid());
                    int count = counts.intValue();
                    teacherBean.setAbnormalClock(count);// 异常打卡次数
                    // 3.异常打卡次数 总的打卡次数 减去正常的打卡次数
                    String sql3 = "SELECT COUNT(*) FROM (SELECT * FROM pass_record_" + tableName
                            + " WHERE person_id = ? GROUP BY DATE_FORMAT(record_time,'%d')) pp";
                    Long totalCounts = (Long) queryRunner.query(sql3, new ScalarHandler(1),
                            teacherBean.getTeacher_thdid());
                    int totalCount = totalCounts.intValue();
                    teacherBean.setNormalClock(totalCount - count);
                } else {
                    teacherBean.setAbnormalClock(0);// 异常打卡次数
                    teacherBean.setNormalClock(0);
                }
            }
            return lists;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<PassRecordBean> getInfoByThdid(String person_id, String month) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        String sql = "select * from pass_record_" + month + " where person_id=? GROUP BY DATE_FORMAT(record_time,'%d')";
        try {
            List<PassRecordBean> passRecordList = queryRunner.query(sql,
                    new BeanListHandler<PassRecordBean>(PassRecordBean.class), person_id);
            return passRecordList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PassRecordBean getNightOutWork(String tableName, String person_id, String nightOutWork) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDatasource());
        String sql = "select * from " + tableName
                + " p where  p.machine_name like '%DT%' and p.person_id = ? and p.record_time >= ? order by p.record_time desc  limit 1";
        try {
            return queryRunner.query(sql, new BeanHandler<>(PassRecordBean.class), person_id, nightOutWork);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }*/
}
